﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Data;
using Aspose.Cells;
using System.Configuration;
using System.IO;

namespace DBUtility
{
    public class OutExcel
    {

        public static DataTable TransformDataTable(DataTable sourceDt, Dictionary<string, string> parsList)
        {
            return OfficeHelper.DataHelper.TransformDataTable(sourceDt, parsList);
        }
        public static void ToExcel(System.Web.HttpResponse response, string filename, DataTable Dt)
        {
            try
            {
                OfficeHelper.ExcelHelper.Output(response, filename, Dt);
            }
            catch { }
        }
        public static bool ToExcel(string filename, DataTable Dt)
        {
            try
            {
                Gift.Common.WebCommon.DeleteFile(HttpContext.Current.Server.MapPath("~/Excel"));
                return OfficeHelper.ExcelHelper.Output(filename, Dt);
            }
            catch { return false; }
        }
        //这个是读取模板然后下载的Excel
        public static string GetExcelFiles_one_askwater(DataTable Dt)
        {
            try
            {
                string path = HttpContext.Current.Server.MapPath("~/loaddown_excel/叫水记录_单个.xls");
                Workbook workbook = new Workbook();
                workbook.Open(path);
                Worksheet worksheet = workbook.Worksheets[0];

                Style style = worksheet.Cells["A3"].GetStyle();//应验(户)  
                string time = DateTime.Now.ToString();
                worksheet.Cells["C2"].PutValue(time);

                int k = 4;
                foreach (DataRow dr in Dt.Rows)
                {
                    worksheet.Cells["A" + k.ToString()].PutValue(dr["brand"].ToString().Trim());
                    worksheet.Cells["B" + k.ToString()].PutValue( int.Parse(dr["Louhao"].ToString().Trim()));
                    worksheet.Cells["C" + k.ToString()].PutValue( int.Parse(dr["sushe_number"].ToString().Trim()));
                    worksheet.Cells["D" + k.ToString()].PutValue(dr["songshui_tongshu"].ToString().Trim());

                    worksheet.Cells["E" + k.ToString()].PutValue(dr["shengyu_tongshu"].ToString().Trim());
                    worksheet.Cells["F" + k.ToString()].PutValue(dr["huishou_tongshu"].ToString().Trim());
                    worksheet.Cells["G" + k.ToString()].PutValue(dr["beizu"].ToString().Trim());
                    worksheet.Cells["H" + k.ToString()].PutValue(dr["songshui_gong"].ToString().Trim());
                    worksheet.Cells["I" + k.ToString()].PutValue(dr["state_text"].ToString().Trim());

                    worksheet.Cells["A" + k.ToString()].SetStyle(style);
                    worksheet.Cells["B" + k.ToString()].SetStyle(style);
                    worksheet.Cells["C" + k.ToString()].SetStyle(style);
                    worksheet.Cells["D" + k.ToString()].SetStyle(style);

                    worksheet.Cells["E" + k.ToString()].SetStyle(style);
                    worksheet.Cells["F" + k.ToString()].SetStyle(style);
                    worksheet.Cells["G" + k.ToString()].SetStyle(style);
                    worksheet.Cells["H" + k.ToString()].SetStyle(style);
                    worksheet.Cells["I" + k.ToString()].SetStyle(style);

                    k++;
                }
                if (!Directory.Exists(System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["download_file"])))
                {
                    Directory.CreateDirectory(System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["download_file"]));
                }
                string hour_time = DateTime.Now.Hour.ToString();
                string hour_now = Convert.ToInt32(hour_time) < 13 ? "中午" : "下午";
                string returnPath = ConfigurationManager.AppSettings["download_file"] + "/" + DateTime.Now.ToString("yyyy_MM_dd_hhmmss") + "" + hour_now + "_送水表打印" + ".xls";
 
                string savePath =  HttpContext.Current.Server.MapPath("~/")+returnPath;
                workbook.Save(savePath);
                return returnPath;
            }
            catch(Exception ex)
            {
                return "";
            }
        }
        public static string GetExcelFiles_all_askwater(string code)
        {
            try
            {

                string path = HttpContext.Current.Server.MapPath("~/loaddown_excel/叫水记录模板.xls");
                Workbook workbook;
                workbook = new Workbook();
                workbook.Open(path);
                DataTable loaddown;
                string[] areey = new string[] { "旧白宫", "四合院", "北区女生宿舍", "新白宫", "西区宿舍", "玫瑰园1", "玫瑰园2", "其他" };
                for (int i = 0; i < areey.Length; i++)
                {
                    loaddown = commen.ask_water_allloaddowm(code, areey[i]);
                    Worksheet worksheet = workbook.Worksheets[i];

                    Style style = worksheet.Cells["A3"].GetStyle();//应验(户)  
                    string time = DateTime.Now.ToString();
                    worksheet.Cells["C2"].PutValue(time);

                    int k = 4;
                    foreach (DataRow dr in loaddown.Rows)
                    {
                        worksheet.Cells["A" + k.ToString()].PutValue(dr["brand"].ToString().Trim());
                        worksheet.Cells["B" + k.ToString()].PutValue( int.Parse(dr["Louhao"].ToString().Trim()));
                        worksheet.Cells["C" + k.ToString()].PutValue( int.Parse(dr["sushe_number"].ToString().Trim()));
                        worksheet.Cells["D" + k.ToString()].PutValue(dr["songshui_tongshu"].ToString().Trim());

                        worksheet.Cells["E" + k.ToString()].PutValue(dr["shengyu_tongshu"].ToString().Trim());
                        worksheet.Cells["F" + k.ToString()].PutValue(dr["qian_songshui"].ToString().Trim());
                        worksheet.Cells["G" + k.ToString()].PutValue(dr["huishou_tongshu"].ToString().Trim());
                        worksheet.Cells["H" + k.ToString()].PutValue(dr["beizu"].ToString().Trim());
                        worksheet.Cells["I" + k.ToString()].PutValue(dr["songshui_gong"].ToString().Trim());
                        worksheet.Cells["J" + k.ToString()].PutValue(dr["state_text"].ToString().Trim());

                        worksheet.Cells["A" + k.ToString()].SetStyle(style);
                        worksheet.Cells["B" + k.ToString()].SetStyle(style);
                        worksheet.Cells["C" + k.ToString()].SetStyle(style);
                        worksheet.Cells["D" + k.ToString()].SetStyle(style);

                        worksheet.Cells["E" + k.ToString()].SetStyle(style);
                        worksheet.Cells["F" + k.ToString()].SetStyle(style);
                        worksheet.Cells["G" + k.ToString()].SetStyle(style);
                        worksheet.Cells["H" + k.ToString()].SetStyle(style);
                        worksheet.Cells["I" + k.ToString()].SetStyle(style);
                        worksheet.Cells["J" + k.ToString()].SetStyle(style);

                        k++;
                    }
                
                }
               
                if (!Directory.Exists(System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["download_file"])))
                {
                    Directory.CreateDirectory(System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["download_file"]));
                }
                string hour_time = DateTime.Now.Hour.ToString();
                string hour_now = Convert.ToInt32(hour_time) < 13 ? "中午" : "下午";
                string returnPath = ConfigurationManager.AppSettings["download_file"] + "/" + DateTime.Now.ToString("yyyy_MM_dd_hhmmss") + "" + hour_now + "_送水表打印" + ".xls";

                string savePath = HttpContext.Current.Server.MapPath("~/") + returnPath;
                workbook.Save(savePath);
                return returnPath;
            }
            catch (Exception ex)
            {
                return "";
            }
        }

        //这个是读取模板然后下载的Excel
        public static string GetExcelFiles_one_dorm(DataTable Dt)
        {
            try
            {
                string path = HttpContext.Current.Server.MapPath("~/loaddown_excel/宿舍套餐模板_单个.xls");
                Workbook workbook = new Workbook();
                workbook.Open(path);
                Worksheet worksheet = workbook.Worksheets[0];

                Style style = worksheet.Cells["A3"].GetStyle();//应验(户)  
                string time = DateTime.Now.ToString();
                worksheet.Cells["D2"].PutValue(time);

                int k = 4;
                foreach (DataRow dr in Dt.Rows)
                {
                    worksheet.Cells["A" + k.ToString()].PutValue(dr["brand"].ToString().Trim());
                    worksheet.Cells["B" + k.ToString()].PutValue(dr["Louhao"].ToString().Trim());
                    worksheet.Cells["C" + k.ToString()].PutValue(dr["sushe_number"].ToString().Trim());
                    worksheet.Cells["D" + k.ToString()].PutValue(dr["tongshu"].ToString().Trim());

                    worksheet.Cells["E" + k.ToString()].PutValue(dr["shengyu_tongshu"].ToString().Trim());
                    worksheet.Cells["F" + k.ToString()].PutValue(dr["qiantong"].ToString().Trim());
                    worksheet.Cells["G" + k.ToString()].PutValue(dr["shuifei"].ToString().Trim());
                    worksheet.Cells["H" + k.ToString()].PutValue(dr["yajin"].ToString().Trim());

                    worksheet.Cells["I" + k.ToString()].PutValue(dr["yishou"].ToString().Trim());
                    worksheet.Cells["J" + k.ToString()].PutValue(dr["shouji"].ToString().Trim());
                    worksheet.Cells["K" + k.ToString()].PutValue(dr["beizu"].ToString().Trim());
                    worksheet.Cells["L" + k.ToString()].PutValue(dr["time"].ToString().Trim());
                    worksheet.Cells["M" + k.ToString()].PutValue(dr["state_text"].ToString().Trim());

                    worksheet.Cells["A" + k.ToString()].SetStyle(style);
                    worksheet.Cells["B" + k.ToString()].SetStyle(style);
                    worksheet.Cells["C" + k.ToString()].SetStyle(style);
                    worksheet.Cells["D" + k.ToString()].SetStyle(style);

                    worksheet.Cells["E" + k.ToString()].SetStyle(style);
                    worksheet.Cells["F" + k.ToString()].SetStyle(style);
                    worksheet.Cells["G" + k.ToString()].SetStyle(style);
                    worksheet.Cells["H" + k.ToString()].SetStyle(style);

                    worksheet.Cells["I" + k.ToString()].SetStyle(style);
                    worksheet.Cells["J" + k.ToString()].SetStyle(style);
                    worksheet.Cells["K" + k.ToString()].SetStyle(style);
                    worksheet.Cells["L" + k.ToString()].SetStyle(style);
                    worksheet.Cells["M" + k.ToString()].SetStyle(style);

                    k++;
                }
                if (!Directory.Exists(System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["download_file"])))
                {
                    Directory.CreateDirectory(System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["download_file"]));
                }
                string hour_time = DateTime.Now.Hour.ToString();
                string hour_now = Convert.ToInt32(hour_time) < 13 ? "中午" : "下午";
                string returnPath = ConfigurationManager.AppSettings["download_file"] + "/" + DateTime.Now.ToString("yyyy_MM_dd_hhmmss") + "" + hour_now + "_送水表打印" + ".xls";

                string savePath = HttpContext.Current.Server.MapPath("~/") + returnPath;
                workbook.Save(savePath);
                return returnPath;
            }
            catch (Exception ex)
            {
                return "";
            }
        }
        public static string GetExcelFiles_all_dorm(string code)
        {
            try
            {

                string path = HttpContext.Current.Server.MapPath("~/loaddown_excel/宿舍套餐模板.xls");
                Workbook workbook;
                workbook = new Workbook();
                workbook.Open(path);
                DataTable loaddown;
                string[] areey = new string[] { "旧白宫", "四合院", "北区女生宿舍", "新白宫", "西区宿舍", "玫瑰园1", "玫瑰园2", "其他" };
                for (int i = 0; i < areey.Length; i++)
                {
                    loaddown = commen.dromlist_ALL_loaddowm(code, areey[i]);
                    Worksheet worksheet = workbook.Worksheets[i];

                    Style style = worksheet.Cells["A3"].GetStyle();//应验(户)  
                    string time = DateTime.Now.ToString();
                    worksheet.Cells["B2"].PutValue(time);

                    int k = 4;
                    foreach (DataRow dr in loaddown.Rows)
                    {
                        worksheet.Cells["A" + k.ToString()].PutValue(dr["brand"].ToString().Trim());
                        worksheet.Cells["B" + k.ToString()].PutValue(dr["Louhao"].ToString().Trim());
                        worksheet.Cells["C" + k.ToString()].PutValue(dr["sushe_number"].ToString().Trim());
                        worksheet.Cells["D" + k.ToString()].PutValue(dr["tongshu"].ToString().Trim());

                        worksheet.Cells["E" + k.ToString()].PutValue(dr["shengyu_tongshu"].ToString().Trim());
                        worksheet.Cells["F" + k.ToString()].PutValue(dr["qiantong"].ToString().Trim());
                        worksheet.Cells["G" + k.ToString()].PutValue(dr["shuifei"].ToString().Trim());
                        worksheet.Cells["H" + k.ToString()].PutValue(dr["yajin"].ToString().Trim());

                        worksheet.Cells["I" + k.ToString()].PutValue(dr["yishou"].ToString().Trim());
                        worksheet.Cells["J" + k.ToString()].PutValue(dr["shouji"].ToString().Trim());
                        worksheet.Cells["K" + k.ToString()].PutValue(dr["beizu"].ToString().Trim());
                        worksheet.Cells["L" + k.ToString()].PutValue(dr["time"].ToString().Trim());
                        worksheet.Cells["M" + k.ToString()].PutValue(dr["state_text"].ToString().Trim());

                        worksheet.Cells["A" + k.ToString()].SetStyle(style);
                        worksheet.Cells["B" + k.ToString()].SetStyle(style);
                        worksheet.Cells["C" + k.ToString()].SetStyle(style);
                        worksheet.Cells["D" + k.ToString()].SetStyle(style);

                        worksheet.Cells["E" + k.ToString()].SetStyle(style);
                        worksheet.Cells["F" + k.ToString()].SetStyle(style);
                        worksheet.Cells["G" + k.ToString()].SetStyle(style);
                        worksheet.Cells["H" + k.ToString()].SetStyle(style);

                        worksheet.Cells["I" + k.ToString()].SetStyle(style);
                        worksheet.Cells["J" + k.ToString()].SetStyle(style);
                        worksheet.Cells["K" + k.ToString()].SetStyle(style);
                        worksheet.Cells["L" + k.ToString()].SetStyle(style);
                        worksheet.Cells["M" + k.ToString()].SetStyle(style);

                        k++;
                    }

                }

                if (!Directory.Exists(System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["download_file"])))
                {
                    Directory.CreateDirectory(System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["download_file"]));
                }
                string hour_time = DateTime.Now.Hour.ToString();
                string hour_now = Convert.ToInt32(hour_time) <13 ? "中午" : "下午";
                string returnPath = ConfigurationManager.AppSettings["download_file"] + "/" + DateTime.Now.ToString("yyyy_MM_dd_hhmmss") + "" + hour_now + "_送水表打印" + ".xls";

                string savePath = HttpContext.Current.Server.MapPath("~/") + returnPath;
                workbook.Save(savePath);
                return returnPath;
            }
            catch (Exception ex)
            {
                return "";
            }
        }

  
    }
}
